package com.global.query;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.util.Assert;

import com.global.base.DateUtil;
import com.global.base.StringUtil;
import com.global.vo.QueryParameter;

/**
 * 带命名参数的解析
 */
public abstract class NamedParameterQuery {

	/**
	 * Set of characters that qualify as parameter separators,
	 * indicating that a parameter name in a SQL String has ended.
	 */
	private static final char[] PARAMETER_SEPARATORS =
			new char[] {'"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+', '-', '*', '%', '/', '\\', '<', '>', '^'};

	/**
	 * Set of characters that qualify as comment or quotes starting characters.
	 */
	private static final String[] START_SKIP = new String[] {"'", "\"", "--", "/*"};

	/**
	 * Set of characters that at are the corresponding comment or quotes ending characters.
	 */
	private static final String[] STOP_SKIP = new String[] {"'", "\"", "\n", "*/"};

	private static List<String> errorMessageList = new ArrayList<String>();
	
	/**
	 * 返回异常信息
	 * @return
	 */
	public static String getErrorMessage() {
		return StringUtil.getString(errorMessageList, ",");
	}
	
	//-------------------------------------------------------------------------
	// Core methods used by NamedParameterJdbcTemplate and SqlQuery/SqlUpdate
	//-------------------------------------------------------------------------

	/**
	 * Parse the SQL statement and locate any placeholders or named parameters.
	 * Named parameters are substituted for a JDBC placeholder.
	 * @param sql the SQL statement
	 * @return the parsed statement, represented as ParsedSql instance
	 */
	public static ParsedSqlQuery parseSqlStatement(String sql) {
		
		Assert.notNull(sql, "SQL must not be null");

		Set<String> namedParameters = new HashSet<String>();
		ParsedSqlQuery parsedSql = new ParsedSqlQuery(sql);

		char[] statement = sql.toCharArray();
		int namedParameterCount = 0;
		int unnamedParameterCount = 0;
		int totalParameterCount = 0;

		int i = 0;
		while (i < statement.length) {
			int skipToPosition = skipCommentsAndQuotes(statement, i);
			if (i != skipToPosition) {
				if (skipToPosition >= statement.length) {
					break;
				}
				i = skipToPosition;
			}
			char c = statement[i];
			if (c == ':' || c == '&') {
				int j = i + 1;
				if (j < statement.length && statement[j] == ':' && c == ':') {
					// Postgres-style "::" casting operator - to be skipped.
					i = i + 2;
					continue;
				}
				while (j < statement.length && !isParameterSeparator(statement[j])) {
					j++;
				}
				if (j - i > 1) {
					String parameter = sql.substring(i + 1, j);
					if (!namedParameters.contains(parameter)) {
						namedParameters.add(parameter);
						namedParameterCount++;
					}
					parsedSql.addNamedParameter(parameter, i, j);
					totalParameterCount++;
				}
				i = j - 1;
			}
			else {
				if (c == '?') {
					unnamedParameterCount++;
					totalParameterCount++;
				}
			}
			i++;
		}
		parsedSql.setNamedParameterCount(namedParameterCount);
		parsedSql.setUnnamedParameterCount(unnamedParameterCount);
		parsedSql.setTotalParameterCount(totalParameterCount);
		
		return parsedSql;
	}

	/**
	 * Skip over comments and quoted names present in an SQL statement
	 * @param statement character array containing SQL statement
	 * @param position current position of statement
	 * @return next position to process after any comments or quotes are skipped
	 */
	private static int skipCommentsAndQuotes(char[] statement, int position) {
		
		for (int i = 0; i < START_SKIP.length; i++) {
			if (statement[position] == START_SKIP[i].charAt(0)) {
				boolean match = true;
				for (int j = 1; j < START_SKIP[i].length(); j++) {
					if (!(statement[position + j] == START_SKIP[i].charAt(j))) {
						match = false;
						break;
					}
				}
				if (match) {
					int offset = START_SKIP[i].length();
					for (int m = position + offset; m < statement.length; m++) {
						if (statement[m] == STOP_SKIP[i].charAt(0)) {
							boolean endMatch = true;
							int endPos = m;
							for (int n = 1; n < STOP_SKIP[i].length(); n++) {
								if (m + n >= statement.length) {
									// last comment not closed properly
									return statement.length;
								}
								if (!(statement[m + n] == STOP_SKIP[i].charAt(n))) {
									endMatch = false;
									break;
								}
								endPos = m + n;
							}
							if (endMatch) {
								// found character sequence ending comment or quote
								return endPos + 1;
							}
						}
					}
					// character sequence ending comment or quote not found
					return statement.length;
				}

			}
		}
		
		return position;
	}
	
	/**
	 * Parse the SQL statement and locate any placeholders or named parameters.
	 * Named parameters are substituted for a JDBC placeholder and any select list
	 * is expanded to the required number of placeholders.  Select lists may contain
	 * an array of objects and in that case the placeholders will be grouped and
	 * enclosed with parantheses.  This allows for the use of "expression lists" in
	 * the SQL statement like:<br/>
	 * select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))
	 * <p>The parameter values passed in are used to determine the number of
	 * placeholder to be used for a select list. Select lists should be limited
	 * to 100 or fewer elements. A larger number of elements is not guaramteed to
	 * be supported by the database and is strictly vendor-dependent.
	 * @param parsedSql the parsed represenation of the SQL statement
	 * @param paramSource the source for named parameters
	 * @return the SQL statement with substituted parameters
	 * @see #parseSqlStatement
	 */
	public static String substituteNamedParameters(ParsedSqlQuery parsedSql, SqlParameterSource paramSource) {
		
		String originalSql = parsedSql.getOriginalSql();
		StringBuilder actualSql = new StringBuilder();
		
		List<String> paramNames = parsedSql.getParameterNames();
		
		int lastIndex = 0;
		
		for (int i = 0; i < paramNames.size(); i++) {
			String paramName = (String) paramNames.get(i);
			
			int[] indexes = parsedSql.getParameterIndexes(i);
			int startIndex = indexes[0];
			int endIndex = indexes[1];
			
			actualSql.append(originalSql.substring(lastIndex, startIndex));
			if (paramSource != null && paramSource.hasValue(paramName)) {
				Object value = paramSource.getValue(paramName);				
				
				if (value instanceof Collection) {
					Iterator entryIter = ((Collection) value).iterator();
					int k = 0;
					while (entryIter.hasNext()) {
						if (k > 0) {
							actualSql.append(", ");
						}
						
						k++;
						Object entryItem = entryIter.next();
						if (entryItem instanceof Object[]) {
							Object[] expressionList = (Object[]) entryItem;
							actualSql.append("(");
							for (int m = 0; m < expressionList.length; m++) {
								if (m > 0) {
									actualSql.append(", ");
								}
								actualSql.append("?");
							}
							actualSql.append(")");
						} else {
							actualSql.append("?");
						}
					}
				} else {
					actualSql.append("?");
				}
			} else {
				actualSql.append("?");
			}
			lastIndex = endIndex;
		}

		actualSql.append(originalSql.substring(lastIndex, originalSql.length()));
		
		return actualSql.toString();
	}

	/**
	 * Convert a Map of named parameter values to a corresponding array.
	 * @param parsedSql the parsed SQL statement
	 * @param paramSource the source for named parameters
	 * @param declaredParams the List of declared SqlParameter objects
	 * (may be <code>null</code>). If specified, the parameter metadata will
	 * be built into the value array in the form of SqlParameterValue objects.
	 * @return the array of values
	 */
	public static Object[] buildValueArray(ParsedSqlQuery parsedSql, SqlParameterSource paramSource, List<SqlParameter> declaredParams) {

		Object[] paramArray = new Object[parsedSql.getTotalParameterCount()];
		if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) {
			throw new InvalidDataAccessApiUsageException(
					"You can't mix named and traditional ? placeholders. You have " +
					parsedSql.getNamedParameterCount() + " named parameter(s) and " +
					parsedSql.getUnnamedParameterCount() + " traditonal placeholder(s) in [" +
					parsedSql.getOriginalSql() + "]");
		}
		List<String> paramNames = parsedSql.getParameterNames();
		for (int i = 0; i < paramNames.size(); i++) {
			String paramName = paramNames.get(i);
			try {
				Object value = paramSource.getValue(paramName);
				SqlParameter param = findParameter(declaredParams, paramName, i);
				paramArray[i] = (param != null ? new SqlParameterValue(param, value) : value);
			}
			catch (IllegalArgumentException ex) {
				throw new InvalidDataAccessApiUsageException(
						"No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage());
			}
		}
		
		return paramArray;
	}

	/**
	 * Find a matching parameter in the given list of declared parameters.
	 * @param declaredParams the declared SqlParameter objects
	 * @param paramName the name of the desired parameter
	 * @param paramIndex the index of the desired parameter
	 * @return the declared SqlParameter, or <code>null</code> if none found
	 */
	private static SqlParameter findParameter(List<SqlParameter> declaredParams, String paramName, int paramIndex) {
		if (declaredParams != null) {
			// First pass: Look for named parameter match.
			for (SqlParameter declaredParam : declaredParams) {
				if (paramName.equals(declaredParam.getName())) {
					return declaredParam;
				}
			}
			// Second pass: Look for parameter index match.
			if (paramIndex < declaredParams.size()) {
				SqlParameter declaredParam = declaredParams.get(paramIndex);
				// Only accept unnamed parameters for index matches.
				if (declaredParam.getName() == null) {
					return declaredParam;
				}
			}
		}
		
		return null;
	}

	/**
	 * Determine whether a parameter name ends at the current position,
	 * that is, whether the given character qualifies as a separator.
	 */
	private static boolean isParameterSeparator(char c) {
		if (Character.isWhitespace(c)) {
			return true;
		}
		for (char separator : PARAMETER_SEPARATORS) {
			if (c == separator) {
				return true;
			}
		}
		
		return false;
	}

	/**
	 * Convert a Map of parameter types to a corresponding int array.
	 * This is necessary in order to reuse existing methods on JdbcTemplate.
	 * Any named parameter types are placed in the correct position in the
	 * Object array based on the parsed SQL statement info.
	 * @param parsedSql the parsed SQL statement
	 * @param paramSource the source for named parameters
	 */
	public static int[] buildSqlTypeArray(ParsedSqlQuery parsedSql, SqlParameterSource paramSource) {
		int[] sqlTypes = new int[parsedSql.getTotalParameterCount()];
		List<String> paramNames = parsedSql.getParameterNames();
		
		for (int i = 0; i < paramNames.size(); i++) {
			String paramName = (String) paramNames.get(i);
			sqlTypes[i] = paramSource.getSqlType(paramName);
		}
		
		return sqlTypes;
	}


	//-------------------------------------------------------------------------
	// Convenience methods operating on a plain SQL String
	//-------------------------------------------------------------------------

	/**
	 * Parse the SQL statement and locate any placeholders or named parameters.
	 * Named parameters are substituted for a JDBC placeholder.
	 * <p>This is a shortcut version of
	 * {@link #parseSqlStatement(String)} in combination with
	 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
	 * @param sql the SQL statement
	 * @return the actual (parsed) SQL statement
	 */
	public static String parseSqlStatementIntoString(String sql) {
		ParsedSqlQuery parsedSql = parseSqlStatement(sql);
		
		return substituteNamedParameters(parsedSql, null);
	}

	/**
	 * Parse the SQL statement and locate any placeholders or named parameters.
	 * Named parameters are substituted for a JDBC placeholder and any select list
	 * is expanded to the required number of placeholders.
	 * <p>This is a shortcut version of
	 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
	 * @param sql the SQL statement
	 * @param paramSource the source for named parameters
	 * @return the SQL statement with substituted parameters
	 */
	public static String substituteNamedParameters(String sql, SqlParameterSource paramSource) {
		ParsedSqlQuery parsedSql = parseSqlStatement(sql);
		
		return substituteNamedParameters(parsedSql, paramSource);
	}

	/**
	 * Convert a Map of named parameter values to a corresponding array.
	 * <p>This is a shortcut version of
	 * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}.
	 * @param sql the SQL statement
	 * @param paramMap the Map of parameters
	 * @return the array of values
	 */
	public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) {
		ParsedSqlQuery parsedSql = parseSqlStatement(sql);
		
		return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null);
	}

	/**
	 * 将命名参数替换为?后的SQL
	 * @param parsedSql  解析后的命名参数信息
	 * @param namedParamters  命名参数清单，可以为null;不为null，则用于定位与替换集合类型的参数
	 * @return
	 */
	public static String getPlaceholdersSQL(ParsedSqlQuery parsedSql, List<QueryParameter> namedParamters) {
		if(parsedSql!=null){
			MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
			if(namedParamters!=null){
				for(QueryParameter queryParameter : namedParamters){
					mapSqlParameterSource.addValue(queryParameter.getName(), queryParameter.getValue());
				}
			}
			
			return substituteNamedParameters(parsedSql, mapSqlParameterSource);
		}
		
		return "";
	}
	
	/**
	 * 将命名参数替换为?后的SQL
	 * @param parsedSql  解析后的命名参数信息
	 * @param namedParamters  命名参数清单，可以为null;不为null，则用于定位与替换集合类型的参数
	 * @return
	 */
	public static String getPlaceholdersSQL(String parsedSql, List<QueryParameter> namedParamters) {
		if(StringUtils.isNotBlank(parsedSql)){
			MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
			if(namedParamters!=null){
				for(QueryParameter queryParameter : namedParamters){
					mapSqlParameterSource.addValue(queryParameter.getName(), queryParameter.getValue());
				}
			}
			
			return substituteNamedParameters(parsedSql, mapSqlParameterSource);
		}
		
		return "";
	}
	
	/**
	 * 返回命名参数对应的值
	 * @param value
	 * @return
	 */
	public static String getNamedParameterValueStr(Object value){
		
		if(value instanceof java.lang.String){
			return "'" + value + "'";
		}else if(value instanceof java.lang.Long){
			return String.valueOf((Long)value);
		}else if(value instanceof java.lang.Integer){
			return String.valueOf((Integer)value);
		}else if(value instanceof java.lang.Double){
			return String.valueOf((Double)value);
		}else if(value instanceof java.lang.Float){
			return String.valueOf((Float)value);
		}else if(value instanceof java.util.Date){
			Date date = (Date)value;
			String javaPattern = "yyyy/MM/dd HH:mm:ss";
			String sqlPattern = "yyyy/mm/dd hh24:mi:ss";
			
			return "to_Date('" + DateUtil.getDateToStr(date, javaPattern) + "', '" + sqlPattern + "')";
		}else if(value instanceof java.sql.Date){
			java.sql.Date date = (java.sql.Date)value;
			String javaPattern = "yyyy/MM/dd HH:mm:ss";
			String sqlPattern = "yyyy/mm/dd hh24:mi:ss";
			
			return "to_Date('" + DateUtil.getDateToStr(date, javaPattern) + "', '" + sqlPattern + "')";						
		}else if(value instanceof java.sql.Timestamp){
			java.sql.Timestamp timestamp = (java.sql.Timestamp)value;
			String javaPattern = "yyyy/MM/dd HH:mm:ss.SSS";
			String sqlPattern = "yyyy/mm/dd hh24:mi:ss:ff";
			
			return "To_Timestamp('" + DateUtil.getDateToStr(timestamp, javaPattern) + "', '" + sqlPattern + "')";
		}else if(value instanceof java.sql.Time){
			java.sql.Time time = (java.sql.Time)value;
			String javaPattern = "HH:mm:ss.SSS";
			String sqlPattern = "hh24:mi:ss:ff";
			
			return "to_Date('" + DateUtil.getDateToStr(time, javaPattern) + "', '" + sqlPattern + "')";			
		}	
		
		return "";
	}
	
	/**
	 * 将参数替换成对应的值后的SQL，形如：Select * From Dba_All_Tables t Where t.Owner = 'SYS'
	 * @param parsedSql
	 * @param paramSource
	 * @return 可以执行的静态SQL, 形如：Select * From Dba_All_Tables t Where t.Owner = 'SYS'
	 */
	public static String getExecutedSQL(ParsedSqlQuery parsedSql, List<QueryParameter> namedParamters) {
		
		errorMessageList.clear();
		
		String originalSql = parsedSql.getOriginalSql();
		StringBuilder actualSql = new StringBuilder();
		
		List<String> paramNames = parsedSql.getParameterNames();
		
		if((namedParamters==null || namedParamters.isEmpty()) && !paramNames.isEmpty()){
			errorMessageList.add("SQL【" + originalSql + "】中存在命名参数【" + StringUtil.getString(paramNames, ",") + "】，所以必须传入相应个数的参数，才可以获取到可执行的SQL");
		    
			return "";
		}else if(namedParamters!=null && namedParamters.size()<paramNames.size()){
			errorMessageList.add("传入的命名参数个数【" + namedParamters.size() + "】必须等于【" + paramNames.size() + "】");
			
			return "";
		}else{
			List<String> notExistNames = new ArrayList<String>();
			for(String name : paramNames){
				boolean isExist = false;
				for(QueryParameter q : namedParamters){
					if(name.equalsIgnoreCase(q.getName())){
						q.setName(name);
						
						isExist = true;
						break;
					}
				}
				if(!isExist){
					notExistNames.add(name);
				}
			}
			if(!notExistNames.isEmpty()){
				errorMessageList.add("传入的命名参数中应该还包含【" + StringUtil.getString(notExistNames, ",") + "】");
				
				return "";
			}
		}
		
		int lastIndex = 0;
		
		for (int i = 0; i < paramNames.size(); i++) {
			String paramName = (String) paramNames.get(i);
			
			int[] indexes = parsedSql.getParameterIndexes(i);
			int startIndex = indexes[0];
			int endIndex = indexes[1];
			
			actualSql.append(originalSql.substring(lastIndex, startIndex));
			
			Object value = null;
			for(QueryParameter q : namedParamters){
				if(q.getName().equalsIgnoreCase(paramName)){
					value = q.getValue();
				}
			}
			
			if (value instanceof Collection) {
				Iterator entryIter = ((Collection) value).iterator();
				int k = 0;
				while (entryIter.hasNext()) {
					if (k > 0) {
						actualSql.append(", ");
					}
					
					k++;
					Object entryItem = entryIter.next();
					if (entryItem instanceof Object[]) {
						Object[] expressionList = (Object[]) entryItem;
						actualSql.append("(");
						for (int m = 0; m < expressionList.length; m++) {
							if (m > 0) {
								actualSql.append(", ");
							}
							actualSql.append(getNamedParameterValueStr(value));
						}
						actualSql.append(")");
					} else {
						actualSql.append(getNamedParameterValueStr(value));
					}
				}
			} else {
				actualSql.append(getNamedParameterValueStr(value));
			}
			
			lastIndex = endIndex;
		}

		actualSql.append(originalSql.substring(lastIndex, originalSql.length()));
		
		return actualSql.toString();
	}
	
	public static void main(String[] args){
		String sql = "Select Substr(t.Owner, 1, 2) Owner1, t.* From Dba_Tab_Columns t Where t.Owner = :Owner And t.Table_Name = :Table_Name";
		ParsedSqlQuery parseSqlStatement = NamedParameterQuery.parseSqlStatement(sql);
		System.out.println("parameterNames = " + StringUtil.getString(parseSqlStatement.getParameterNames(), ","));
		
		List<QueryParameter> namedParamters = new ArrayList<QueryParameter>();
		namedParamters.add(new QueryParameter("owner", "SYS"));
		String executedSQL = NamedParameterQuery.getExecutedSQL(parseSqlStatement, namedParamters);
		System.out.println("executedSQL = " + executedSQL);
	}
}
